﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for CalculatePremiumDAL
/// </summary>
public class CalculatePremiumDAL
{
	public CalculatePremiumDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}


    public decimal getPremium(String relationship,String plan,int age)
    {

        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "SELECT PlanType, FromAge, ToAge, Premium FROM INSURANCE_PREMIUM WHERE (@age BETWEEN FromAge AND ToAge) and PlanType=@plan ";
        
        
        if("Gold".Equals(plan)||"Silver".Equals(plan)||"Bronze".Equals(plan)){
            sql = sql + "and Relationship=@relationship";
        }

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@age", age);
        cmd.Parameters.AddWithValue("@plan", plan);
        cmd.Parameters.AddWithValue("@relationship", relationship);

        SqlDataReader dr = cmd.ExecuteReader();
        Decimal premium = 0;

        while (dr.Read())
        {
           premium = dr.GetDecimal(3);
          
        }
        dr.Close();
        con.Close();

       
        return premium;

    }



    

}